package com.mathlearning.auth.repository;

import com.mathlearning.auth.entity.UserSubmission;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Optional;

@Repository
public interface UserSubmissionRepository extends JpaRepository<UserSubmission, Long> {
    
    /**
     * 查询用户对某题的最新提交记录
     */
    Optional<UserSubmission> findFirstByUserIdAndProblemIdOrderBySubmitTimeDesc(Long userId, Long problemId);
    
    /**
     * 查询用户的提交记录（分页）
     */
    Page<UserSubmission> findByUserIdOrderBySubmitTimeDesc(Long userId, Pageable pageable);
    
    /**
     * 查询用户特定状态的提交记录（分页）
     */
    Page<UserSubmission> findByUserIdAndStatusOrderBySubmitTimeDesc(Long userId, String status, Pageable pageable);
    
    /**
     * 统计用户在某课程下通过的题目数
     */
    @Query("SELECT COUNT(DISTINCT s.problemId) FROM UserSubmission s " +
           "JOIN Problem p ON s.problemId = p.id " +
           "WHERE s.userId = :userId AND p.courseId = :courseId AND s.status = 'accepted'")
    long countAcceptedProblemsByUserIdAndCourseId(@Param("userId") Long userId, @Param("courseId") Long courseId);
    
    /**
     * 检查用户是否通过某题
     */
    boolean existsByUserIdAndProblemIdAndStatus(Long userId, Long problemId, String status);
    
    /**
     * 统计用户总做题数
     */
    @Query("SELECT COUNT(*) FROM UserSubmission s WHERE s.userId = :userId")
    Long countByUserId(@Param("userId") Long userId);
    
    /**
     * 统计用户答对题数
     */
    @Query("SELECT COUNT(*) FROM UserSubmission s WHERE s.userId = :userId AND s.status = 'accepted'")
    Long countCorrectByUserId(@Param("userId") Long userId);
    
    /**
     * 查询用户所有答题记录（不分页，按时间倒序）
     */
    @Query("SELECT s FROM UserSubmission s WHERE s.userId = :userId ORDER BY s.submitTime DESC")
    java.util.List<UserSubmission> findAllByUserIdOrderBySubmitTimeDesc(@Param("userId") Long userId);
    
    /**
     * 查询用户在指定年月的学习记录日期（去重）
     * 使用原生SQL查询，返回日期列表
     */
    @Query(value = "SELECT DISTINCT DATE(submit_time) as learning_date " +
                   "FROM user_submission " +
                   "WHERE user_id = :userId " +
                   "AND YEAR(submit_time) = :year " +
                   "AND MONTH(submit_time) = :month " +
                   "ORDER BY learning_date", 
           nativeQuery = true)
    java.util.List<java.sql.Date> findLearningDatesByUserIdAndYearMonth(
        @Param("userId") Long userId, 
        @Param("year") Integer year, 
        @Param("month") Integer month
    );
    
    /**
     * 统计指定时间范围内的不重复题目数（用于本周答题统计）
     */
    @Query("SELECT COUNT(DISTINCT s.problemId) FROM UserSubmission s " +
           "WHERE s.userId = :userId " +
           "AND s.submitTime >= :startTime " +
           "AND s.submitTime <= :endTime")
    Long countDistinctProblemsByDateRange(
        @Param("userId") Long userId,
        @Param("startTime") java.time.LocalDateTime startTime,
        @Param("endTime") java.time.LocalDateTime endTime
    );
}





